﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace FiyatDegisikligi
{
    public class DataLayer
    {
        #region Ctor
        private static DataLayer _instance = null;
        private DataLayer()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = Program.Session[Settings.ServerName].ToString();
            builder.InitialCatalog = Program.Session[Settings.DatabaseName].ToString();
            builder.MultipleActiveResultSets = true;

            if (Program.Session[Settings.AuthenticateMode].ToString() == "Sql")
            {
                builder.UserID = Program.Session[Settings.UserName].ToString();
                builder.Password = Program.Session[Settings.Password].ToString();
                builder.IntegratedSecurity = false;
            }
            else
                builder.IntegratedSecurity = true;

            this.CnnStr = builder.ToString();
        }
        public static DataLayer Get
        {
            get
            {
                if (_instance == null)
                    _instance = new DataLayer();
                return _instance;
            }
        }
        #endregion

        public string CnnStr { get; set; }

        public SqlDataReader Reader(string sqlText, params SqlParameter[] prm)
        {
            SqlConnection cn = new SqlConnection(this.CnnStr);
            cn.Open();
            SqlCommand cmd = new SqlCommand(sqlText, cn);
            if (prm != null)
                cmd.Parameters.AddRange(prm);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public SqlDataReader Reader(string sqlText)
        {
            return Reader(sqlText, null);
        }

        public SqlTransaction OpenTrn()
        {
            SqlConnection cn = new SqlConnection(this.CnnStr);
            cn.Open();
            return cn.BeginTransaction();
        }

        public void ExecTrn(string sqlText, SqlTransaction trn, params SqlParameter[] prm)
        {
            using (SqlCommand cmd = new SqlCommand(sqlText, trn.Connection))
            {
                cmd.Transaction = trn;
                if (prm != null)
                    cmd.Parameters.AddRange(prm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
        }

        public void Exec(string sqlText, params SqlParameter[] prm)
        {
            using (SqlConnection cn = new SqlConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                {
                    if (prm != null)
                        cmd.Parameters.AddRange(prm);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                cn.Close();
            }
        }
        public void Exec(string sqlText)
        {
            Exec(sqlText, null);
        }

        public DataTable Fill(string sqlText, params SqlParameter[] prm)
        {
            DataTable dt = new DataTable();
            using (SqlConnection cn = new SqlConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                {
                    if (prm != null)
                        cmd.Parameters.AddRange(prm);
                    using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                    {
                        adp.Fill(dt);
                        cmd.Parameters.Clear();
                    }
                }
                cn.Close();
            }
            return dt;
        }
        public DataTable Fill(string sqlText)
        {
            return Fill(sqlText, null);
        }

        public T ExecScalar<T>(string sqlText, params SqlParameter[] prm)
        {
            object sonuc = null;
            using (SqlConnection cn = new SqlConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                {
                    if (prm != null)
                        cmd.Parameters.AddRange(prm);
                    sonuc = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
                cn.Close();
            }
            if (sonuc != DBNull.Value)
                return (T)Convert.ChangeType(sonuc, typeof(T), null);
            else
                return (T)Cevir.DefaultSetting(typeof(T).ToString());
        }
        public T ExecScalar<T>(string sqlText)
        {
            object sonuc = null;
            using (SqlConnection cn = new SqlConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                {
                    sonuc = cmd.ExecuteScalar();
                }
                cn.Close();
            }
            if (sonuc != DBNull.Value)
                return (T)Convert.ChangeType(sonuc, typeof(T), null);
            else
                return (T)Cevir.DefaultSetting(typeof(T).ToString());
        }
    }
}
